"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : 防篡改
Case Name   : 三权分立关闭时验证SCHEMA下有independent用户表时，做ALL TABLES赋权，非同名SCHEMA
Create At   : 2022-03-30
@zou_jialiang0501080719
Description :
    1.查询参数enableSeparationOfDuty值
    2.创建用户
    3.创建schema并赋权给用户1
    4.切换independent用户创建表1
    5.切换用户sysadmin创建表2
    6.all tables赋权给普通用户
    7.清理环境
Expect      :
    1.默认值off
    2.成功
    3.成功
    4.成功
    5.成功
    6.失败
    7.成功
History     :
"""


import os
import unittest
from yat.test import macro
from testcase.utils.Common import Common
from testcase.utils.Constant import Constant
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger


class ModifyCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.schema_name = 's_security_indentprivilege_0040'
        self.table_name = 't_security_indentprivilege_0040'
        self.user_name = 'u_security_indentprivilege_0040'
        self.user_privileges = {f'{self.user_name}_indent': 'independent',
                                f'{self.user_name}_sys': 'sysadmin',
                                f'{self.user_name}_noruser': ''}
        self.default_value = self.common.show_param('enableSeparationOfDuty')

    def test_security(self):
        text = '----step1:查询参数enableSeparationOfDuty值; expect:默认off----'
        self.log.info(text)
        if self.default_value != 'off':
            mod_msg = self.pri_sh.execute_gsguc('set',
                                                self.constant.GSGUC_SUCCESS_MSG,
                                                'enableSeparationOfDuty=off')
            self.assertTrue(mod_msg, '执行失败:' + text)
            result = self.pri_sh.restart_db_cluster()
            self.assertTrue(result, '执行失败' + text)

        text = '-----step2：创建用户 expect:成功-----'
        self.log.info(text)
        for user, privilege in self.user_privileges.items():
            create_cmd = f"drop user if exists {user} cascade; " \
                f"create user {user} {privilege} " \
                f"password '{macro.PASSWD_REPLACE}'; "
            msg = self.primary_sh.execut_db_sql(create_cmd)
            self.log.info(msg)
            self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG and
                          self.constant.CREATE_ROLE_SUCCESS_MSG, msg,
                          '执行失败:' + text)

        text = '-----step3：创建schema并赋权限 expect:成功-----'
        self.log.info(text)
        sql_cmd = self.primary_sh.execut_db_sql(f'''
                  drop schema if exists {self.schema_name};
                  create schema {self.schema_name};
                  grant all on schema {self.schema_name} 
                  to {self.user_name}_indent;''')
        self.log.info(sql_cmd)
        self.assertIn(self.constant.DROP_SCHEMA_SUCCESS_MSG, sql_cmd,
                      '执行失败:' + text)
        self.assertIn(self.constant.CREATE_SCHEMA_SUCCESS_MSG, sql_cmd,
                      '执行失败:' + text)
        self.assertIn(self.constant.GRANT_SUCCESS_MSG, sql_cmd,
                      '执行失败:' + text)

        text = '----step4:切换independent用户创建表1 expect:成功----'
        self.log.info(text)
        sql_cmd = self.primary_sh.execut_db_sql(f'''
            set role {self.user_name}_indent password '{macro.PASSWD_REPLACE}';
            create table {self.schema_name}.{self.table_name}
            (id int,address text);''')
        self.log.info(sql_cmd)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, sql_cmd,
                      "执行失败:" + text)
        self.assertIn(self.constant.SET_SUCCESS_MSG, sql_cmd,
                      "执行失败:" + text)

        text = '----step5:切换sys用户创建表2 expect:成功----'
        self.log.info(text)
        sql_cmd = self.primary_sh.execut_db_sql(f'''
            set role {self.user_name}_sys password '{macro.PASSWD_REPLACE}';
            create table {self.schema_name}.{self.table_name}_2
            (id int,address text);''')
        self.log.info(sql_cmd)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, sql_cmd,
                      "执行失败:" + text)
        self.assertIn(self.constant.SET_SUCCESS_MSG, sql_cmd,
                      "执行失败:" + text)

        text = '----step5:all tables赋权给普通用户 expect:失败----'
        self.log.info(text)
        sql_cmd = self.primary_sh.execut_db_sql(f'''
            grant all on all tables in schema {self.schema_name} to 
            {self.user_name}_noruser;''')
        self.log.info(sql_cmd)
        self.assertIn(f'no privileges were granted for "{self.table_name}"',
                      sql_cmd, "执行失败:" + text)

    def tearDown(self):
        text = '----step6:清理环境 expect:成功----'
        self.log.info(text)
        self.log.info('删除模式')
        sql_cmd = self.primary_sh.execut_db_sql(f'''
                   drop schema {self.schema_name} cascade;''')
        self.log.info(sql_cmd)
        self.log.info('删除用户')
        drop_msg = []
        for user in self.user_privileges.keys():
            drop_cmd = f"drop user if exists {user} cascade; "
            msg = self.primary_sh.execut_db_sql(drop_cmd)
            self.log.info(msg)
            drop_msg.append(msg)
        self.assertEqual(3,
                         drop_msg.count(
                             self.constant.DROP_ROLE_SUCCESS_MSG),
                         '执行失败:' + text)
        self.assertIn(self.constant.DROP_SCHEMA_SUCCESS_MSG, sql_cmd,
                      "执行失败:" + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
